package com.test08.excelpoi02;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.google.gson.Gson;

import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Boolean;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/*
 * 用来处理1.xls中的站点中信息的类
 * 
 */
public class ExcelTest02<V> {
	public static <E> void main(String[] args) throws Exception {
		jxl.Workbook readwb = null;
		// 构建Workbook对象, 只读Workbook对象
		// 直接从本地文件创建Workbookx
		InputStream instream = new FileInputStream("src/1.xls");
		readwb = Workbook.getWorkbook(instream);

		Sheet readsheet = readwb.getSheet(0);
		int rsColumns = readsheet.getColumns();
		int rsRows = readsheet.getRows();
		// 假设真实数据从第4行开始的
		ArrayList<NFCBean> list = new ArrayList<NFCBean>();
		String station = null;
		for (int i = 3; i < rsRows; i++) {
			NFCBean nfcPojo = new NFCBean();
			try {
				for (int j = 0; j < rsColumns; j++) {
					String contents;
					switch (j) {
					case 2:
						contents = readsheet.getCell(j, i).getContents().trim();
						if (contents != "") {
							station = contents;
						}
						nfcPojo.setStation(station);
						break;
					case 8:
						contents = readsheet.getCell(j, i).getContents().trim();
						nfcPojo.setCount0(new BigDecimal(contents));
						break;
					case 9:
						contents = readsheet.getCell(j, i).getContents().trim();
						nfcPojo.setCount1(new BigDecimal(contents));
						break;
					case 10:
						contents = readsheet.getCell(j, i).getContents().trim();
						nfcPojo.setCount2(new BigDecimal(contents));
						break;
					case 11:
						contents = readsheet.getCell(j, i).getContents().trim();
						nfcPojo.setCount3(new BigDecimal(contents));
						break;
					case 12:
						contents = readsheet.getCell(j, i).getContents().trim();
						nfcPojo.setCount4(new BigDecimal(contents));
						break;
					default:
						break;
					}
				}
				list.add(nfcPojo);
			} catch (Exception e) {
			}
		}
		// 得到了站台-数据集合的对应关系的集合
		List<SummaryBean> summary = getCount(list);

		// 甚至可以转成json数据
		System.out.println(new Gson().toJson(summary));

		// 该线路所有站点的数据总和
		int sumStation = 0;
		// 该线路所有站点的通过总数
		int sumPass = 0;
		for (SummaryBean summaryBean : summary) {
			sumStation += summaryBean.getNfcBeans().size();
			sumPass += summaryBean.getPassCount();
		}
		System.out.println("该线路总共有：" + sumStation + "条记录");
		System.out.println("该线路通过了：" + sumPass + "个");
		System.out.println("通过率：" + sumPass * 1.0 / sumStation);

	}


	public static List<SummaryBean> getCount(List<NFCBean> list) throws Exception {
		// 最后这个map中就是一个station对应一个同样station的List集合
		Map<String, List<NFCBean>> map = new HashMap<>();
		for (NFCBean nfcPojo : list) {
			// 判断这个map中是否已经存储过这个station
			List<NFCBean> listTest = map.get(nfcPojo.getStation());
			if (listTest == null) {
				// 第一次存
				listTest = new ArrayList<>();
				listTest.add(nfcPojo);
				map.put(nfcPojo.getStation(), listTest);
			} else {
				// 曾经存过
				listTest.add(nfcPojo);
				map.put(nfcPojo.getStation(), listTest);
			}
		}

		List<SummaryBean> summaryBeans = new ArrayList<>();
		// 开始遍历这map集合,转存到List中
		Set<String> stations = map.keySet();
		for (String station : stations) {
			summaryBeans.add(new SummaryBean(station, map.get(station)));
		}

		return summaryBeans;
	}

}
